﻿Imports System.Windows.Forms

Public Class LendUpdate1
    Private Sub InputUpdate_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Dim 开始时间 As Date = New Date(Now.Year, Now.Month, 1)
        '开始时间 = 开始时间.AddYears(-1)
        'Dim 结束时间 As Date = New Date(Now.Year, Now.Month, Date.DaysInMonth(Now.Year, Now.Month))

        'Me.dt开始时间.Value = 开始时间
        'Me.dt结束时间.Value = 结束时间

        LoadData1(0)
    End Sub

#Region "查询上一个月按钮"
    Public Sub LoadData1(ByVal itype As Integer)

        Dim strBegin As String = Me.dt开始时间.Value.Year.ToString("0000") + "-" + Me.dt开始时间.Value.Month.ToString("00") + "-" + Me.dt开始时间.Value.Day.ToString("00")
        Dim strEnd As String = Me.dt结束时间.Value.Year.ToString("0000") + "-" + Me.dt结束时间.Value.Month.ToString("00") + "-" + Me.dt结束时间.Value.Day.ToString("00")

        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT WysBorrowLend.*  ")
        sb.AppendLine(" ,WysInputUser1.UserName as UserName1")
        sb.AppendLine(" ,WysInputUser2.UserName as UserName2")
        sb.AppendLine(",ifnull((select SUM(CrashStatusCount) from CrashInOut where InOutID=WysBorrowLend.ID  AND CrashInOut.type=4 group by CrashInOut.InOutID),0) as 还入金额")
        sb.AppendLine("  from WysBorrowLend ")
        sb.AppendLine("INNER join WysBorrowLend WysBorrowLend1  on WysBorrowLend.LendBorrowUserName =WysBorrowLend1.ID ")
        sb.AppendLine("left join WysInputUser WysInputUser1 on WysInputUser1.id =WysBorrowLend1.LendBorrowUserName ")
        sb.AppendLine("left join WysInputUser WysInputUser2 on WysInputUser2.id =WysBorrowLend.username ")

        sb.AppendLine(" WHERE date(WysBorrowLend.DateTime)>='" + strBegin + "'")
        sb.AppendLine(" AND date(WysBorrowLend.DateTime)<='" + strEnd + "'")
        If rad借给被人钱.Checked Then
            sb.AppendLine(" and WysBorrowLend.type = 1")
        Else
            sb.AppendLine(" and WysBorrowLend.type = 3")
        End If
        Dim dtDataTable As DataTable = ct.FillTable(sb.ToString)
        Dim dv As DataView = dtDataTable.DefaultView
        dv.Sort = "UserName1,DATETIME DESC"
        dtDataTable = dv.ToTable

        Dim dtNew As New DataTable
        dtNew.TableName = "收入"

        dtNew.Columns.Add("编号")
        dtNew.Columns.Add("还入时间")
        dtNew.Columns.Add("借款人")
        dtNew.Columns.Add("金额")
        dtNew.Columns.Add("备注")
        dtNew.Columns.Add("操作用户")

        For Each dr As DataRow In dtDataTable.Rows
            dtNew.Rows.Add(New String() {转换对象字符串(dr("ID")), 转换日期字符串(dr("DATETIME")), 转换对象字符串(dr("UserName1")), _
                                                                     转换对象字符串(dr("COUNT")), 转换对象字符串(dr("MEMO")), _
                                                                      转换对象字符串(dr("USERNAME2"))})
        Next

        Dim dvNew As DataView = dtNew.DefaultView
        dvNew.Sort = "还入时间 desc"
        dtNew = dvNew.ToTable

        Me.OutlookGrid1.AutoGenerateColumns = False
        Me.OutlookGrid1.BindData(dtNew, "支出")
        Me.OutlookGrid1.Columns(0).Visible = False

    End Sub

    Private Function 转换对象字符串(ByVal ob As Object) As String
        If ob Is DBNull.Value Then
            Return ""
        End If
        Return ob.ToString
    End Function

    Private Function 转换对象字符串2(ByVal ob As Object) As String
        If ob Is DBNull.Value Then
            Return "未还"
        End If

        If ob.ToString = "1" Then
            Return "已还"
        ElseIf ob.ToString = "2" Then
            Return "在还中"
        Else
            Return "未还"
        End If
    End Function

    Private Function 转换日期字符串(ByVal ob As Object) As String
        Dim dt As Date = Date.Parse(ob.ToString())
        Return dt.ToString("yyyy年MM月dd日")
    End Function
#End Region

#Region "删除CHECK"
    Public Overrides Function DoDeleteCheck() As Boolean
        DoDeleteCheck = False
        If Me.OutlookGrid1.SelectedRows.Count = 0 Then
            ShowMessageBox("没有选中任何记录")
            Exit Function
        End If

        If Me.OutlookGrid1.SelectedRows.Count = 0 Then Exit Function
        Dim id As String = Me.OutlookGrid1.SelectedRows(0).Cells(0).Value.ToString
        If id.Length <> 36 Then
            ShowMessageBox("没有选中任何记录")
            Exit Function
        End If
        strid = id

        If ShowMessageResult("是否删除本条记录") = Windows.Forms.DialogResult.No Then Exit Function

        DoDeleteCheck = True
    End Function
#End Region

#Region "重载删除事件"

    Private Sub UpdateCrashInOut()

        Dim sb As New StringBuilder
        Dim dt As New DataTable
        Dim rs As ADODB.Recordset = ct.GetTable("SELECT * from CrashInOut  where LendId='" + strid + "'")
        dt = ct.FillTable(rs)
        If dt.Rows.Count > 0 Then
            Dim cmdSub As ADODB.Command = New ADODB.Command
            cmdSub.CommandType = ADODB.CommandTypeEnum.adCmdText
            For Each dr As DataRow In dt.Rows
                If CStr(dr("CrashStatusID")) <> "" AndAlso CDec(dr("CrashStatusCount")) > 0 Then
                    sb.Length = 0
                    If rad借给被人钱.Checked Then
                        sb.AppendLine("Update CrashStatus set Count=Count -" & CStr(dr("CrashStatusCount")) & " Where id='" & CStr(dr("CrashStatusID")) + "'")
                    Else
                        sb.AppendLine("Update CrashStatus set Count=Count +" & CStr(dr("CrashStatusCount")) & " Where id='" & CStr(dr("CrashStatusID")) + "'")
                    End If
                    cmdSub.CommandText = sb.ToString
                    ct.ExcCommand(cmdSub)
                    UpdateCrashOptime(ct, CStr(dr("CrashStatusID")))

                    If rad借给被人钱.Checked = True Then
                        '插入金额 2022-11-20
                        Account_Detail.Update还入_减(ct, CDate(dr("DATETIME")).ToString("yyyy-MM-dd"), dr("USERNAME").ToString, CDec(dr("CrashStatusCount")))
                        Crash_Detail.Update还入_减(ct, CDate(dr("DATETIME")).ToString("yyyy-MM-dd"), dr("CrashStatusID").ToString, CDec(dr("CrashStatusCount")))
                    Else
                        '插入金额 2022-11-20
                        Account_Detail.Update还出_减(ct, CDate(dr("DATETIME")).ToString("yyyy-MM-dd"), dr("USERNAME").ToString, CDec(dr("CrashStatusCount")))
                        Crash_Detail.Update还出_减(ct, CDate(dr("DATETIME")).ToString("yyyy-MM-dd"), dr("CrashStatusID").ToString, CDec(dr("CrashStatusCount")))
                    End If

                End If
            Next
        End If
        ct.GetTable("DELETE from CrashInOut  where LendId='" + strid + "'")
    End Sub

    Dim strid As String = ""
    Public Overrides Function DoDelete() As Boolean

        UpdateCrashInOut()

        Dim CMD As New ADODB.Command
        CMD.CommandText = "DELETE from WysBorrowLend  where ID='" + strid + "'"
        If ct.ExcCommand(CMD) Then
            ShowMessageBox("删除成功")
        Else
            ShowMessageBox("删除失败")
        End If

        LoadData1(0)
        Return True
    End Function
#End Region

#Region "重载插入"
    Public Overrides Function DoInsert() As Boolean
        MyBase.DoInsert()
        Using frm As New LendAdd1
            If rad借给被人钱.Checked Then
                frm.参数列表.Add("TYPE", "1")
            Else
                frm.参数列表.Add("TYPE", "3")
            End If
            frm.Text = "还入添加"
            frm.iType = Me.iType
            frm.strName = Me.strName
            frm.ShowDialog()
            If frm.bAdd Then
                LoadData1(0)
            End If
        End Using
        Return True
    End Function
#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        LoadData1(0)
    End Sub

    Private Sub dt开始时间_ValueChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dt结束时间.ValueChanged, dt开始时间.ValueChanged
        Dim 开始时间 As Integer = CInt(dt开始时间.Value.ToString("yyyyMMdd"))
        Dim 结束时间 As Integer = CInt(dt结束时间.Value.ToString("yyyyMMdd"))
        If 开始时间 > 结束时间 Then
            Exit Sub
        End If
    End Sub

    Private Sub OutlookGrid1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OutlookGrid1.DoubleClick
        If Me.OutlookGrid1.SelectedRows.Count = 0 Then Exit Sub
        Dim id As String = Me.OutlookGrid1.SelectedRows(0).Cells(0).Value.ToString
        If id.Length = 36 Then
            Using frm As LendAdd1 = New LendAdd1
                frm.Text = "还入更新"
                frm.参数列表.Add("ID", id)
                If rad借给被人钱.Checked Then
                    frm.参数列表.Add("TYPE", "1")
                Else
                    frm.参数列表.Add("TYPE", "3")
                End If
                frm.模式 = Wys.Control.Vb.InsertFormBase.模式型.修正
                frm.iType = Me.iType
                frm.strName = Me.strName
                frm.ShowDialog()
                If frm.bAdd Then
                    LoadData1(0)
                End If
            End Using
        End If
    End Sub

    Public Overrides Sub 保存窗体数据到数据库(ByRef dtData As System.Data.DataTable)
        '年
        dtData.Rows.Add(New String() {"开始时间", "", dt开始时间.Value.ToString("yyyy-MM-dd")})
        dtData.Rows.Add(New String() {"结束时间", "", dt结束时间.Value.ToString("yyyy-MM-dd")})

        If rad借给被人钱.Checked Then
            dtData.Rows.Add(New String() {"借给被人钱", "", "是"})
        Else
            dtData.Rows.Add(New String() {"借给被人钱", "", "否"})
        End If

        MyBase.保存窗体数据到数据库(dtData)
    End Sub

    Public Overrides Sub 存取数据库数据到窗体(ByVal dtDbData As System.Data.DataTable)

        If dtDbData.Rows.Count > 0 Then
            '年
            dt开始时间.Value = 查找数据("开始时间", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            dt结束时间.Value = 查找数据("结束时间", "", Now.ToString("yyyy-MM-dd"), dtDbData)

            rad借给被人钱.Checked = IIf(查找数据("借给被人钱", "", "是", dtDbData) = "是", True, False)
            rad向别人借钱.Checked = IIf(查找数据("借给被人钱", "", "是", dtDbData) = "否", True, False)

        End If
        MyBase.存取数据库数据到窗体(dtDbData)
    End Sub
End Class
